USE [NANS21V]
GO

/****** Object:  StoredProcedure [dbo].[RankingList]    Script Date: 06/12/2014 11:36:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RankingList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[RankingList]
GO

CREATE PROCEDURE [dbo].[RankingList]
	@TAIKAINO int,
	@KYOGINO int,
	@SUBNO int,
	@ROUNDNO int,
	@DIVERGE int, --0:トラック　1:高跳び・幅跳び・投擲
	@SYUMOKUKUBUN int,
	@SUBSYUMOKUKUBUN int,
	--@KONSEINO int,
	@SUBKIROKUKUBUN int
AS

	BEGIN
		DECLARE @SQL AS VARCHAR(MAX)

	SET @SQL = '
			select*from(
			SELECT DISTINCT '

		IF @DIVERGE = 1 
			BEGIN
				SET @SQL = @SQL + ' dbo.Kaze('+ STR(@SYUMOKUKUBUN) +','+ STR(@SUBSYUMOKUKUBUN) +', 
								CASE WHEN RE.KIROKU = 0 THEN '''' 
								ELSE dbo.Kiroku(RE.KIROKU,'+ STR(@SUBSYUMOKUKUBUN) +','+ STR(@SUBKIROKUKUBUN) +',(CASE WHEN CONVERT(varchar, RE.EXTEND1) = ''1'' then 3 else RE.EXTEND2 END)) END, RE.EXTEND1) AS KIROKUS,'
			END
		ELSE 
			BEGIN
				SET @SQL = @SQL + '
								CONVERT(varchar, CASE WHEN RE.KIROKU = 0 THEN '''' ELSE dbo.Kiroku(RE.KIROKU,SM.SYUMOKUKUBUN,SM.KIROKUKUBUN,(CASE WHEN ISNUMERIC(re.EXTEND2)  = 1 then re.EXTEND2 else 3 END)) END) + ''<br/>'' + CONVERT(varchar, RE.EXTEND1) AS KIROKUS,
								'
			END

	SET @SQL = @SQL + '
					CASE WHEN RE.PLUS = '''' THEN 99999 ELSE RE.JYUNI END AS PLUSJYUNI,
					CASE WHEN RE.EXTEND1 = '''' THEN 0 ELSE CONVERT(numeric(2,1) ,RE.EXTEND1)*1000 END AS EXTEND1,
					RE.RANKVAL,
					CASE WHEN RE.RANK= 0 THEN '''' ELSE RE.RANK END AS RANK,
					(CASE WHEN '+ STR(@SYUMOKUKUBUN) +' = 4 THEN CONVERT(varchar,KS.KYOGISYANO) ELSE (CONVERT(varchar,KS.NUMBER1) + CONVERT(varchar,KS.NUMBER2))END) AS NUMBER, 
					KS.KYOGISYANO,
					CONVERT(varchar, KS.KYOGISYAKANA) + ''<br/>''  +  CONVERT(varchar, KS.KYOGISYAMEI) AS KYOGISYA,
					CONVERT(varchar, S.SYOZOKUMEI) + ''<br/>'' + CONVERT(varchar, KS.KOJINSYOZOKUTIMEI) AS SYOZOKU,
					SK.OPENFLG, 
					LA.KUMINO,
					LA.LANE,
					dbo.Passage(RE.PLUS) AS PLUS,
					dbo.Jyuni(RE.JYUNI,SK.OPENFLG) AS JYUNI,
					(CASE WHEN RE.JYUNI IS NULL THEN NULL WHEN RE.JYUNI=0 THEN NULL ELSE RE.JYUNI END) AS Sort,
					RE.KIROKU AS KIROKU,
					LA.SYUKKETU,
					( CASE 
						WHEN RE.KIROKU IS NULL THEN 999999999.999
						WHEN RE.KIROKU =0 THEN 999999999.999 
						 ELSE RE.KIROKU END) AS KIROKU_MOTO  
					FROM ('
				SET @SQL = @SQL + '
					SELECT KUMINO,
						   LANE,
						   KIROKU,
						   JYUNI,
						   RCOMMENT,
						   PLUS,
						   RANK AS RANKVAL,
						   CONVERT(varchar, RANK) AS RANK,
						   EXTEND1,
						   EXTEND2,
						   EXTEND3,
						   EXTEND4,
						   EXTEND5
					 FROM TBL_RESULT
					 WHERE --KIROKU > 0 AND 
						TAIKAINO = '+ STR(@TAIKAINO) +'
						AND KYOGINO = '+ STR(@KYOGINO) +'
						AND SUBNO = '+ STR(@SUBNO) +'
						AND ROUNDNO = '+ STR(@ROUNDNO) +''
				
	SET @SQL = @SQL + '
				) AS RE
				 LEFT OUTER JOIN TBL_LANE AS LA
				   ON RE.KUMINO = LA.KUMINO AND RE.LANE = LA.LANE,
					TBL_KYOGISYA AS KS,
					TBL_SYOZOKUTI AS ST,
					TBL_SYOZOKU AS S,
					TBL_SANKAKYOGI AS SK, 
					TBL_KYOGI AS KY,  --0508add
					TBL_SYUMOKU_MASTER AS SM  --0508add
				WHERE	KS.TAIKAINO = LA.TAIKAINO
					AND LA.KYOGISYANO = KS.KYOGISYANO
					AND LA.TAIKAINO = SK.TAIKAINO 
					AND LA.KYOGISYANO = SK.KYOGISYANO 
					AND LA.KYOGINO = SK.KYOGINO 
					AND KS.TAIKAINO = S.TAIKAINO
					AND KS.SYOZOKUNO1 = S.SYOZOKUNO
					AND S.SYOZOKUTINO = ST.SYOZOKUTINO
					AND LA.TAIKAINO = '+ STR(@TAIKAINO) +'
					AND LA.KYOGINO = '+ STR(@KYOGINO) +'
					AND LA.SUBNO = '+ STR(@SUBNO) +'
					AND LA.ROUNDNO = '+ STR(@ROUNDNO) +'
					AND KY.TAIKAINO = LA.TAIKAINO --0508add
					AND KY.KYOGINO = LA.KYOGINO --0508add
					AND KY.SYUMOKUNO = SM.SYUMOKUNO --0508add
					AND LA.SYUKKETU <> 2'

		IF @DIVERGE = 1 
			BEGIN
				SET @SQL = @SQL + '
									) X ORDER BY CASE WHEN X.OPENFLG <> 0 THEN 1 ELSE 0 END, 
												 CASE WHEN X.KIROKU = 0 THEN 1 ELSE 0 END ASC, 
												 X.KIROKU DESC, 
												 X.EXTEND1 ASC, 
												 X.KUMINO ASC, 
												 X.LANE ASC'
			END
		ELSE 
			BEGIN
				SET @SQL = @SQL + '
									) X ORDER BY CASE WHEN X.OPENFLG <> 0 THEN 1 ELSE 0 END, 
												 CASE WHEN X.KIROKU = 0 THEN 1 ELSE 0 END ASC, 
												 X.KIROKU ASC, 
												 X.EXTEND1 ASC, 
												 X.KUMINO ASC, 
												 X.LANE ASC'
			END

		--PRINT @SQL
		EXECUTE(@SQL)
	END


	--ORDER BY RANK
	--ORDER BY KIROKU 
GO

